Create Calendar Table, Measures and Calculated Columns 8
     Step 1. Create Date Table.

 To create Date table follow the Gif below. After create Date table go to model view and make relationship between shark attacks table and date table.

      


     Step 2.

     After create the Date table ,next create Measures and Calculated Column. You can find the measures list below and you will find a word document by clicking  on the Red icon on top  download and save it.


        Measures and Calculated Column :

    Measures:

  table name = SAUSA50YRS     (SharkAttacksUSA50Years)

1.     .Total Attacks=  COUNTROWS(DISTINCT(SAUSA50YRS[Case Number-ID]))

 

2.     .Avg Attacks Per Year = AVERAGEX(VALUES('Date'[Date].[Year]),[.Total Attacks])

 

3.     .SA10YrsAgo = CALCULATE(SAUSA50YRS[.Avg Attacks Per Year],DATEADD('Date'[Date], -10,YEAR))

 

4.     .DOD% = DIVIDE(([.Avg Attacks Per Year] - [.SA10YrsAgo]),[.SA10YrsAgo])

 

5.     .ArrowForDOD% = SWITCH(TRUE(),[.DOD%] <0,UNICHAR(9660),UNICHAR(9650))

 

6.      .ColorForArrow = IF([.ArrowForDOD%] = UNICHAR(9660),"Green","Red")

 

 

    Calculated Column

.Decades = VAR DecadeStart =

    ROUNDDOWN ( 'Date'[Date].[Year], -1 )

VAR DecadeEnd = DecadeStart + 9

RETURN

    DecadeStart & " - " & DecadeEnd



Very useful video about how to create measures:

https://www.youtube.com/watch?v=MMZ0D8kSoMw

Create Calendar Table, Measures and Calculated Columns 8
     Step 1. Create Date Table.

 To create Date table follow the Gif below. After create Date table go to model view and make relationship between shark attacks table and date table.

      


     Step 2.

     After create the Date table ,next create Measures and Calculated Column. You can find the measures list below and you will find a word document by clicking  on the Red icon on top  download and save it.


        Measures and Calculated Column :

    Measures:

  table name = SAUSA50YRS     (SharkAttacksUSA50Years)

1.     .Total Attacks=  COUNTROWS(DISTINCT(SAUSA50YRS[Case Number-ID]))

 

2.     .Avg Attacks Per Year = AVERAGEX(VALUES('Date'[Date].[Year]),[.Total Attacks])

 

3.     .SA10YrsAgo = CALCULATE(SAUSA50YRS[.Avg Attacks Per Year],DATEADD('Date'[Date], -10,YEAR))

 

4.     .DOD% = DIVIDE(([.Avg Attacks Per Year] - [.SA10YrsAgo]),[.SA10YrsAgo])

 

5.     .ArrowForDOD% = SWITCH(TRUE(),[.DOD%] <0,UNICHAR(9660),UNICHAR(9650))

 

6.      .ColorForArrow = IF([.ArrowForDOD%] = UNICHAR(9660),"Green","Red")

 

 

    Calculated Column

.Decades = VAR DecadeStart =

    ROUNDDOWN ( 'Date'[Date].[Year], -1 )

VAR DecadeEnd = DecadeStart + 9

RETURN

    DecadeStart & " - " & DecadeEnd



Very useful video about how to create measures:

https://www.youtube.com/watch?v=MMZ0D8kSoMw